#!/usr/bin/env node

const Database = require('better-sqlite3');
const db = new Database('./data/delivery-management.db');

console.log('🔍 分析对象类（classification_name）分布...\n');

// 1. 分析设备表中的对象类
console.log('1. 分析devices表中的对象类分布:');
const deviceClasses = db.prepare(`
  SELECT 
    d.classification_name,
    d.specialty,
    COUNT(DISTINCT d.device_id) as device_count,
    COUNT(DISTINCT d.classification_code) as classification_code_count,
    GROUP_CONCAT(DISTINCT d.classification_code) as classification_codes
  FROM devices d
  WHERE d.classification_name IS NOT NULL AND d.classification_name != ''
  GROUP BY d.classification_name, d.specialty
  ORDER BY d.specialty, d.classification_name
`).all();

console.log(`找到 ${deviceClasses.length} 个不同的对象类`);

let totalDevices = 0;
const specialtyStats = {};

deviceClasses.forEach(cls => {
  totalDevices += cls.device_count;
  
  if (!specialtyStats[cls.specialty]) {
    specialtyStats[cls.specialty] = {
      classCount: 0,
      deviceCount: 0
    };
  }
  specialtyStats[cls.specialty].classCount++;
  specialtyStats[cls.specialty].deviceCount += cls.device_count;
  
  console.log(`\n📋 ${cls.classification_name} (${cls.specialty})`);
  console.log(`   设备数量: ${cls.device_count}`);
  console.log(`   分类代码数: ${cls.classification_code_count}`);
  console.log(`   分类代码: ${cls.classification_codes}`);
});

console.log(`\n📊 总计统计:`);
console.log(`- 对象类总数: ${deviceClasses.length}`);
console.log(`- 设备总数: ${totalDevices}`);

console.log(`\n🏢 按专业统计:`);
Object.entries(specialtyStats).forEach(([specialty, stats]) => {
  console.log(`${specialty}: ${stats.classCount}个对象类, ${stats.deviceCount}个设备`);
});

// 2. 分析device_type_mappings表中的对象类
console.log(`\n\n2. 分析device_type_mappings表中的对象类映射:`);
const mappingClasses = db.prepare(`
  SELECT 
    dtm.classification_name,
    dtm.specialty,
    dtm.english_name,
    COUNT(*) as mapping_count,
    GROUP_CONCAT(DISTINCT dtm.classification_code) as classification_codes
  FROM device_type_mappings dtm
  WHERE dtm.classification_name IS NOT NULL AND dtm.classification_name != ''
  GROUP BY dtm.classification_name, dtm.specialty, dtm.english_name
  ORDER BY dtm.specialty, dtm.classification_name
`).all();

console.log(`映射表中有 ${mappingClasses.length} 个对象类映射`);

mappingClasses.forEach(cls => {
  console.log(`\n🔗 ${cls.classification_name} -> ${cls.english_name} (${cls.specialty})`);
  console.log(`   映射数量: ${cls.mapping_count}`);
  console.log(`   分类代码: ${cls.classification_codes}`);
});

// 3. 分析专业前缀映射
const specialtyPrefixes = {
  '配管专业': 'piping',
  '电气专业': 'electrical',
  '暖通专业': 'hvac',
  '通信专业': 'communication',
  '仪表专业': 'instrumentation'
};

// 4. 检查现有的实例表是否完整
console.log(`\n\n3. 检查现有实例表完整性:`);

const expectedTables = new Set();
const existingTables = new Set();

// 生成期望的表名列表
mappingClasses.forEach(cls => {
  if (cls.english_name && specialtyPrefixes[cls.specialty]) {
    const expectedTableName = `device_instances_${specialtyPrefixes[cls.specialty]}_${cls.english_name}`;
    expectedTables.add(expectedTableName);
  }
});

// 获取现有的实例表
const actualTables = db.prepare(`
  SELECT name FROM sqlite_master 
  WHERE type='table' AND name LIKE 'device_instances_%'
  ORDER BY name
`).all();

actualTables.forEach(table => {
  existingTables.add(table.name);
});

console.log(`期望的实例表数量: ${expectedTables.size}`);
console.log(`现有的实例表数量: ${actualTables.length}`);

// 找出缺失的表
const missingTables = [...expectedTables].filter(table => !existingTables.has(table));
const extraTables = [...existingTables].filter(table => !expectedTables.has(table));

if (missingTables.length > 0) {
  console.log(`\n❌ 缺失的表 (${missingTables.length}个):`);
  missingTables.forEach(table => console.log(`   - ${table}`));
}

if (extraTables.length > 0) {
  console.log(`\n⚠️  额外的表 (${extraTables.length}个):`);
  extraTables.slice(0, 10).forEach(table => console.log(`   - ${table}`));
  if (extraTables.length > 10) {
    console.log(`   ... 还有 ${extraTables.length - 10} 个表`);
  }
}

// 5. 生成理想的实例表结构建议
console.log(`\n\n4. 理想的实例表结构建议:`);
console.log(`基于对象类的实例表应该有 ${expectedTables.size} 个:`);

const tablesBySpecialty = {};
expectedTables.forEach(tableName => {
  Object.entries(specialtyPrefixes).forEach(([specialty, prefix]) => {
    if (tableName.startsWith(`device_instances_${prefix}_`)) {
      if (!tablesBySpecialty[specialty]) {
        tablesBySpecialty[specialty] = [];
      }
      tablesBySpecialty[specialty].push(tableName);
    }
  });
});

Object.entries(tablesBySpecialty).forEach(([specialty, tables]) => {
  console.log(`\n${specialty}: ${tables.length}个表`);
  tables.slice(0, 5).forEach(table => {
    console.log(`   - ${table}`);
  });
  if (tables.length > 5) {
    console.log(`   ... 还有 ${tables.length - 5} 个表`);
  }
});

db.close();